package com.kalix.training.course.biz;

import com.kalix.framework.core.api.persistence.JsonData;
import com.kalix.framework.core.impl.biz.ShiroGenericBizServiceImpl;
import com.kalix.framework.core.util.MD5Util;
import com.kalix.training.course.api.biz.ICourseBeanService;
import com.kalix.training.course.api.dao.ICourseBeanDao;
import com.kalix.training.course.api.dto.*;
import com.kalix.training.course.biz.util.JsonUtil;
import com.kalix.training.course.biz.util.SendMail;
import com.kalix.training.course.entities.CourseBean;
import org.json.JSONObject;

import java.security.GeneralSecurityException;
import java.util.Date;
import java.util.List;
import java.util.SortedMap;
import java.util.TreeMap;

public class CourseBeanServiceImpl extends ShiroGenericBizServiceImpl<ICourseBeanDao, CourseBean>
        implements ICourseBeanService {
    @Override
    public JsonData getCourse(Integer page, Integer limit, String jsonStr) {
        String sql = "select c.id,u.name as uname,c.name,c.photo,c.introduction,c.points,c.classHour,c.credit,c.uid,c.did,c.gid,d.content as dname,g.name as gname,c.audit from sys_user u,mooc_coursecategory g,mooc_departments d,mooc_course c where c.uid=u.id and c.did=d.id and c.gid=g.id";
        if (jsonStr != null && !jsonStr.isEmpty()) {
            JSONObject obj = new JSONObject(jsonStr);
            for (String str : obj.keySet()) {
                sql += " and c." + str.replace("%", "") + " like '%" + obj.get(str) + "%'";
            }
        }
        List<AllCourseDTO> vplist = dao.findByNativeSql(sql, AllCourseDTO.class);
        List<AllCourseDTO> list = vplist.subList(page * limit - limit, limit * page > vplist.size() ? vplist.size() : limit * page);
        JsonData jsondata = new JsonData();
        jsondata.setData(list);
        jsondata.setTotalCount((long) vplist.size());
        return jsondata;
    }


    @Override
    public JsonData getUidBySelect() {
        List<SelectDTO> list = dao.findByNativeSql("select u.id as value,u.name as label from sys_user u", SelectDTO.class);
        JsonData jsondata = new JsonData();
        jsondata.setData(list);
        jsondata.setTotalCount((long) list.size());
        return jsondata;
    }

    @Override
    public JsonData getGidBySelect() {
        List<SelectDTO> list = dao.findByNativeSql("select g.id as value,g.name as label from mooc_coursecategory g", SelectDTO.class);
        JsonData jsondata = new JsonData();
        jsondata.setData(list);
        jsondata.setTotalCount((long) list.size());
        return jsondata;
    }

    // 客户端中课程排行10条数据
    @Override
    public JsonData getCourseVideo() {
//        String sql = "SELECT c.id,c.audit,c.classhour,c.introduction,c.points,c.name,c.credit,c.photo,u.name as uname,u.id as uid,v.id as videoid,v.hits,v.videourl,v.title,v.audit as vaudit,v.vid" +
//                "FROM mooc_course c,mooc_videos v,sys_user u where c.id = v.cid and c.uid = u.id   ORDER BY v.hits desc LIMIT 10";
        String sql = "select c.id,c.name,c.credit,c.classhour,sum(v.hits) as hits,u.name as uname,u.id as uid,c.photo\n" +
                "from mooc_course c,mooc_videos v,sys_user u\n" +
                "where c.id=v.cid and c.uid=u.id\n" +
                "GROUP BY c.id,c.name,c.credit,c.classhour,u.name,u.id,c.photo\n" +
                "ORDER BY sum(v.hits) desc\n" +
                "limit 10";
        List<CourseVideoDTO> list = dao.findByNativeSql(sql, CourseVideoDTO.class);
        JsonData jsonData = new JsonData();
        jsonData.setData(list);
        jsonData.setTotalCount((long) list.size());
        return jsonData;
    }

    @Override
    public JsonData getCourseByData(String gid, String name, String classHour, String credit, String year, String sort) {
        String sql = "SELECT distinct c.* from mooc_course c,mooc_videos v where 1=1";
        if (gid != null && !gid.equals("")) {
            sql += " and c.gid=" + Long.parseLong(gid);
        }
        if (name != null && !name.equals("")) {
            sql += " and c.name like '%" + name + "%'";
        }
        if (credit != null && !credit.equals("")) {
            if (credit.contains("-")) {
                int startCredit = Integer.parseInt(credit.substring(0, credit.indexOf("-")));
                int endCredit = Integer.parseInt(credit.substring(credit.indexOf("-") + 1, credit.length()));
                if (endCredit >= startCredit) {
                    sql += " and c.Credit between " + startCredit + " and " + endCredit;
                }
            } else if (credit.contains("以上")) {
                int credits = Integer.parseInt(credit.substring(0, credit.indexOf("分以上")));
                sql += " and c.credit >= " + credits;
            }
        }
        if (classHour != null && !classHour.equals("")) {
            if (classHour.contains("-")) {
                int startHour = Integer.parseInt(classHour.substring(0, classHour.indexOf("-")));
                int endHour = Integer.parseInt(classHour.substring(classHour.indexOf("-") + 1, classHour.length()));
                if (endHour >= startHour) {
                    sql += " and c.classHour between " + startHour + " and " + endHour;
                }
            } else if (classHour.contains("以上")) {
                int Hour = Integer.parseInt(classHour.substring(0, classHour.indexOf("以上")));
                sql += " and c.classHour >= " + Hour;
            }
        }
        if (year != null && !year.equals("")) {
            sql += " and CAST(c.updatedate AS VARCHAR(4)) like '" + year + "'";
        }
        if (sort != null && !sort.equals("")) {
            if (sort.contains("hits")) {
                sql += " and v.cid = c.id order by sum(v.hits) desc";
            } else {
                sql += " and v.cid = c.id order by c." + sort + " desc";
            }
        }
//        else {
//            sql += " GROUP BY c.id";
//        }
        JsonData jsondata = new JsonData();
        List<CourseBean> list = dao.findByNativeSql(sql, CourseBean.class);
        if (list.size() > 0) {
            jsondata.setData(list);
            jsondata.setTotalCount((long) list.size());
        }
        return jsondata;
    }

    /**
     * 发送邮件
     * 链接失效
     * */
    @Override
    public String getEmail(String email) throws GeneralSecurityException {
        JSONObject jsonObject = new JSONObject();
        String sql = "SELECT u.id,u.password from sys_user u where email = '"+email+"'";
        List<UserDTO> list = dao.findByNativeSql(sql, UserDTO.class);
        if (list != null && list.size() > 0) {
            Boolean b = SendMail.sendMail(email,list.get(0).getId());
            if(b == true){
                jsonObject.put("flag","1"); // 发送成功
                Date date = new Date();
                long t1=System.currentTimeMillis();
                String time = ""+t1;
                List<UserDTO> userDTOList = dao.findByNativeSql("SELECT l.id,l.uid,l.time FROM mooc_linktime l ORDER BY l.id DESC", UserDTO.class);
                Long idd = null;
                if (userDTOList != null && !userDTOList.isEmpty()) {
                    UserDTO userDTO = userDTOList.get(0);
                    if (userDTO != null) {
                        idd = userDTO.getId()+1;
                    } else {
                        idd = 1L;
                    }
                }

                dao.updateNativeQuery("INSERT INTO mooc_linktime (id,uid,time) VALUES ("+idd+","+list.get(0).getId()+",'"+time+"')");

                return jsonObject.toString();
            } else {
                jsonObject.put("flag","2"); // 发送失败
                return jsonObject.toString();
            }
        }
        jsonObject.put("flag","3"); // 查无此人
        return jsonObject.toString();
    }

    @Override
    public String getPassword(long id, String password) {
//        System.out.println(id);
//        byte[] bytes = id.getBytes();
//        byte[] byteIds = AESUtils.decrypt(bytes,"123");
//        String stri = new String(byteIds);
//        long idzz = Long.parseLong(stri);
        JSONObject jsonObject = new JSONObject();
        List<UserDTO> list = dao.findByNativeSql("SELECT u.id,u.password from sys_user u where id = " + id , UserDTO.class);
        if (list.size() == 1 ) {
            String str = "update sys_user set password = '" + MD5Util.encode(password) + "' where id = " + id;
            int s= dao.updateNativeQuery(str);
            if ( s > 0) {
                jsonObject.put("msg","修改成功");
                return jsonObject.toString();
            } else {
                jsonObject.put("msg","修改失败");
                return jsonObject.toString();
            }
        }
        jsonObject.put("msg","修改失败");
        return jsonObject.toString();
    }



    @Override
    public String getValidationEmail(String email) {
        JSONObject obj = new JSONObject();
        String sql = "SELECT u.email from sys_user u where u.email = '"+email+"'";
        List<UserDTO> list = dao.findByNativeSql(sql, UserDTO.class);
        if (list.isEmpty()) {
            obj.put("key", "1");
            return obj.toString();
        }
        obj.put("key", "2"); // 邮箱已存在
        return obj.toString();
    }

    @Override
    public String getValidate(long id) {
        String sql = "SELECT uid,max(time) as time from mooc_linktime where uid = "+ id +" GROUP BY uid";
        List<UserDTO> list = dao.findByNativeSql(sql, UserDTO.class);
        long tim = Long.parseLong(list.get(0).getTime());
        Date date = new Date();
        if(date.getTime() - tim>300000){
            return "链接已失效";
        }
        return "请修改密码";
    }

    @Override
    public JsonData getCourseById(long id) {
        String sql = "select u.name as uname,c.id,c.name,c.classHour,c.credit,c.photo,c.introduction,c.points,g.name as gname from mooc_course c,mooc_coursecategory g,sys_user u where c.uid = u.id and c.gid = g.id and c.id=" + id;
        List<CourseDTO> list = dao.findByNativeSql(sql, CourseDTO.class);
        JsonData jsonData = new JsonData();
        jsonData.setData(list);
        jsonData.setTotalCount((long) list.size());
        return jsonData;
    }

    /**
     * 根据教师id查询该教师的所有课程信息
     * @param uid
     * @return
     */
    @Override
    public JsonData getCourseAllByUid(long uid) {
        String sql = "select c.id,u.name as uname,c.name,c.photo,c.introduction,c.points,c.classHour,c.credit,c.uid,c.did,c.gid,d.content as dname,g.name as gname,c.audit from sys_user u,mooc_coursecategory g,mooc_departments d,mooc_course c where c.uid=u.id and c.did=d.id and c.gid=g.id and c.uid =" + uid;
        List<AllCourseDTO> clist = dao.findByNativeSql(sql, AllCourseDTO.class);
        JsonData jsonData = new JsonData();
        jsonData.setData(clist);
        jsonData.setTotalCount((long) clist.size());
        return jsonData;
    }

    /**
     *
     * @param str 查询内容/条件（前台传回的参数）
     * @return 根据条件查询出来的课程信息
     */
    @Override
    public String getAllBySearch(String str) {
        String sql = "select c.id,c.photo,c.classHour,c.credit,c.introduction,c.name,d.content as dname,g.name as gname,u.id as uid,u.name as uname,u.intro as uintroduction " +
                "from mooc_course c,mooc_departments d,sys_user u,mooc_coursecategory g " +
                "where c.did = d.id and c.uid = u.id and c.gid = g.id";
        String usersql = "select id as uid,icon,name from sys_user where 1=1";
        if(str != null && str != "") {
            sql += " and (c.name like '%" + str + "%' or d.content like '%" + str + "%' or u.name like '%" + str + "%')";
            usersql += " and name like '%" + str + "%'";
        }
        List<AllCourseDTO> list = dao.findByNativeSql(sql, AllCourseDTO.class);
        List<AllCourseDTO> glist = dao.findByNativeSql(sql + " and gid=1", AllCourseDTO.class);
        List<AllCourseDTO> jlist = dao.findByNativeSql(sql + " and gid=2", AllCourseDTO.class);
        List<AllCourseDTO> wlist = dao.findByNativeSql(sql + " and gid=3", AllCourseDTO.class);
        List<UserDTO> zlist = dao.findByNativeSql(usersql, UserDTO.class);
        SortedMap<Object, Object> params = new TreeMap<Object, Object>();
        params.put("allcourse", list);
        params.put("gongkai", glist);
        params.put("jingpin", jlist);
        params.put("weike", wlist);
        params.put("zhibo", zlist);
        String json = "";
        json = JsonUtil.toJson(params);
        return json;
    }
}
